Day29：前端設計 - 統整全部程式碼、實際Demo影片 - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

2025 iThome 鐵人賽

DAY 29

自我挑戰組

基於 Oracle 資料庫的醫院電子病歷系統設計與建置系列第 29 篇

Day29：前端設計 - 統整全部程式碼、實際Demo影片

17th鐵人賽

zhenn

2025-10-11 20:52:04

23 瀏覽

分享至

大家好！今天是距離鐵人賽的倒數一天啦！！！
今天要分享的有兩個部分：
1. 後端的全部程式碼
2. 實際Demo影片
首先一樣先分享今天要用到的檔案(已上傳的會打勾✅)

oracle-test/
├── backend/
│   ├── middleware/
│   │   └── auth.js
│   ├── routes/
│   │   ├── auth.js ✅
│   │   ├── patients.js ✅
│   │   └── records.js ✅
│   ├── database.js ✅
│   ├── package-lock.json
│   ├── package.json
│   └── server.js ✅
├── frontend/
│   ├── index.html ✅
│   ├── patients.html ✅
│   ├── patients.js ✅
│   ├── records.css ✅
│   ├── records.html ✅
│   ├── records.js ✅
│   ├── script.js ✅
│   └── style.css ✅
├── .env
├── server.js
├── test-oracle11g.js
└── test.js

database.js

const oracledb = require('oracledb');

// 初始化Thick mode
try {
  oracledb.initOracleClient({ libDir: 'C:\\app\\lucy0\\product\\11.2.0\\dbhome_1\\bin' });
  console.log('✅ Oracle Thick mode 已啟用');
} catch (err) {
  console.log('❌ Thick mode 初始化失敗:', err.message);
}

// 資料庫配置
const dbConfig = {
  user: process.env.DB_USER || 'zhenn',
  password: process.env.DB_PASSWORD || '412570211',
  connectString: process.env.DB_CONNECTION_STRING || 'localhost:1521/orcl'
};

// 取得連線
async function getConnection() {
  try {
    return await oracledb.getConnection(dbConfig);
  } catch (err) {
    console.error('取得資料庫連線失敗:', err);
    throw err;
  }
}

// 關閉連線
async function closeConnection(connection) {
  try {
    if (connection) {
      await connection.close();
    }
  } catch (err) {
    console.error('關閉資料庫連線失敗:', err);
  }
}

module.exports = {
  getConnection,
  closeConnection
};

auth.js

const express = require('express');
const oracledb = require('oracledb');
const router = express.Router();
const db = require('../database');

// 簡化登入（實際應該用密碼驗證）
router.post('/login', async (req, res) => {
  const { staffId } = req.body;
  
  let connection;
  try {
    connection = await db.getConnection();
    
    const sql = 'SELECT STAFFID, STAFFNAME, STAFFROLE FROM STAFF WHERE STAFFID = :staffId';
    const result = await connection.execute(sql, { staffId }, { outFormat: oracledb.OUT_FORMAT_OBJECT });
    
    if (result.rows.length === 0) {
      return res.status(401).json({ error: '使用者不存在' });
    }
    
    const user = result.rows[0];
    res.json({
      message: '登入成功',
      user: {
        staffId: user.STAFFID,
        username: user.STAFFNAME,
        role: user.STAFFROLE
      }
    });
    
  } catch (error) {
    console.error('登入失敗:', error);
    res.status(500).json({ error: '登入失敗' });
  } finally {
    if (connection) await db.closeConnection(connection);
  }
});

module.exports = router;

patients.js

const express = require('express');
const oracledb = require('oracledb');
const router = express.Router();
const db = require('../database');
const { authenticate, authorize } = require('../middleware/auth');

// 測試路由 - 確認路由是否正常
router.get('/test', (req, res) => {
  console.log('✅ /api/patients/test 路由正常');
  res.json({ message: 'Patients API 工作正常', status: 'success' });
});

// 搜尋病患
router.get('/search', authenticate, async (req, res) => {
  const { query } = req.query;
  
  console.log('🎯 收到搜尋請求，條件:', query);

  if (!query) {
    return res.status(400).json({ error: '請提供搜尋條件' });
  }

  let connection;
  try {
    console.log('🔗 連接資料庫...');
    connection = await db.getConnection();
    console.log('✅ 資料庫連線成功');
    
    const sql = `
      SELECT 
        p.PATIENTID as PATIENT_ID,
        p.PATIENTNAME as NAME,
        p.PATIENTGENDER as GENDER,
        TO_CHAR(p.PATIENTBIRTH, 'YYYY-MM-DD') as BIRTHDAY,
        p.BLOODTYPE as BLOOD_TYPE,
        p.PATIENTPHONE as PHONE,
        p.PATIENTADDRESS as ADDRESS,
        p.PATIENTIDENTITYNUMBER as ID_NUMBER,
        p.EMERGENCYPHONE as EMERGENCY_CONTACT,
        r.FAMILYHISTORY as FAMILY_HISTORY,
        r.HABITS as BAD_HABITS,
        r.ALLERGYHISTORY as ALLERGY
      FROM PATIENTS p
      LEFT JOIN RECORDS r ON p.PATIENTID = r.PATIENTID
      WHERE p.PATIENTNAME LIKE :query 
         OR p.PATIENTIDENTITYNUMBER LIKE :query
         OR p.PATIENTID LIKE :query
    `;
    
    const result = await connection.execute(sql, {
      query: `%${query}%`
    }, { outFormat: oracledb.OUT_FORMAT_OBJECT });
    
    console.log('✅ 查詢成功，找到', result.rows.length, '筆資料');
    
    res.json({ patients: result.rows });
    
  } catch (error) {
    console.error('❌ 搜尋病患失敗:', error);
    res.status(500).json({ error: '搜尋失敗: ' + error.message });
  } finally {
    if (connection) {
      try {
        await db.closeConnection(connection);
      } catch (closeError) {
        console.error('關閉連線錯誤:', closeError);
      }
    }
  }
});

// 更新病患資料 - 使用 PUT 方法
router.put('/:id', authenticate, async (req, res) => {
  const { id } = req.params;
  const patientData = req.body;
  
  console.log('📝 更新病患資料，ID:', id);
  console.log('📦 更新資料:', patientData);

  // 驗證必要欄位
  const requiredFields = ['name', 'gender', 'birthday', 'id_number'];
  const missingFields = requiredFields.filter(field => !patientData[field]);
  
  if (missingFields.length > 0) {
    return res.status(400).json({ 
      error: '缺少必要欄位', 
      missing: missingFields 
    });
  }

  let connection;
  try {
    connection = await db.getConnection();
    connection.autoCommit = false;
    
    console.log('🔄 更新 PATIENTS 表...');
    // 1. 更新 PATIENTS 表
    const updatePatientSql = `
      UPDATE PATIENTS SET 
        PATIENTNAME = :name,
        PATIENTGENDER = :gender,
        PATIENTBIRTH = TO_DATE(:birthday, 'YYYY-MM-DD'),
        BLOODTYPE = :blood_type,
        PATIENTPHONE = :phone,
        PATIENTADDRESS = :address,
        PATIENTIDENTITYNUMBER = :id_number,
        EMERGENCYPHONE = :emergency_contact
      WHERE PATIENTID = :patient_id
    `;
    
    const patientResult = await connection.execute(updatePatientSql, {
      name: patientData.name,
      gender: patientData.gender,
      birthday: patientData.birthday,
      blood_type: patientData.blood_type || null,
      phone: patientData.phone || null,
      address: patientData.address || null,
      id_number: patientData.id_number,
      emergency_contact: patientData.emergency_contact || null,
      patient_id: id
    });
    
    console.log('✅ PATIENTS 表更新成功，影響行數:', patientResult.rowsAffected);

    // 2. 更新或插入 RECORDS 表
    console.log('🔄 處理 RECORDS 表...');
    const checkRecordSql = 'SELECT COUNT(*) as count FROM RECORDS WHERE PATIENTID = :patient_id';
    const recordResult = await connection.execute(checkRecordSql, { patient_id: id }, { outFormat: oracledb.OUT_FORMAT_OBJECT });

    const recordCount = recordResult.rows[0].COUNT;
    console.log('📊 RECORDS 表記錄數:', recordCount);

    if (recordCount > 0) {
      // 更新現有記錄
      const updateRecordSql = `
        UPDATE RECORDS SET 
          FAMILYHISTORY = :family_history,
          HABITS = :bad_habits,
          ALLERGYHISTORY = :allergy
        WHERE PATIENTID = :patient_id
      `;
      
      const recordUpdateResult = await connection.execute(updateRecordSql, {
        family_history: patientData.family_history || null,
        bad_habits: patientData.bad_habits || null,
        allergy: patientData.allergy || null,
        patient_id: id
      });
      
      console.log('✅ RECORDS 表更新成功，影響行數:', recordUpdateResult.rowsAffected);
    
    } else {
  // 插入新記錄
  console.log('🆕 插入新 RECORDS 記錄...');
  
  // 🚨 先檢查 RECORDS 表的完整結構
  console.log('🔍 檢查 RECORDS 表結構...');
  const tableStructure = await connection.execute(
    `SELECT column_name, data_type, nullable 
     FROM user_tab_columns 
     WHERE table_name = 'RECORDS' 
     ORDER BY column_id`,
    {},
    { outFormat: oracledb.OUT_FORMAT_OBJECT }
  );
  
  console.log('📊 RECORDS 表完整結構:');
  tableStructure.rows.forEach(col => {
    console.log(`   ${col.COLUMN_NAME} (${col.DATA_TYPE}) - NULLABLE: ${col.NULLABLE}`);
  });

  // 查詢最大的 RECORDSID
  const maxIdResult = await connection.execute(
    'SELECT NVL(MAX(RECORDSID), 0) + 1 as NEW_ID FROM RECORDS', 
    {}, 
    { outFormat: oracledb.OUT_FORMAT_OBJECT }
  );
  
  const newRecordsId = maxIdResult.rows[0].NEW_ID;
  console.log('🆔 新 RECORDSID:', newRecordsId);

  // 🚨 根據實際表結構動態建立 INSERT 語句
  const columns = [];
  const values = [];
  const bindParams = {};
  
  // 必填欄位
  columns.push('RECORDSID'); values.push(':records_id'); bindParams.records_id = newRecordsId;
  columns.push('PATIENTID'); values.push(':patient_id'); bindParams.patient_id = id;
  columns.push('APPOINTMENTID'); values.push(':appointment_id'); bindParams.appointment_id = 1; // 固定值
  
  // 可選欄位
  columns.push('FAMILYHISTORY'); values.push(':family_history'); bindParams.family_history = patientData.family_history || null;
  columns.push('HABITS'); values.push(':bad_habits'); bindParams.bad_habits = patientData.bad_habits || null;
  columns.push('ALLERGYHISTORY'); values.push(':allergy'); bindParams.allergy = patientData.allergy || null;

  const insertRecordSql = `
    INSERT INTO RECORDS (${columns.join(', ')})
    VALUES (${values.join(', ')})
  `;
  
  console.log('🔍 最終 INSERT SQL:', insertRecordSql);
  console.log('🔍 綁定參數:', bindParams);

  const recordInsertResult = await connection.execute(insertRecordSql, bindParams);
  
  console.log('✅ RECORDS 表插入成功，影響行數:', recordInsertResult.rowsAffected);
}

    // 提交交易
    await connection.commit();
    console.log('✅ 病患資料更新成功');
    
    res.json({ 
      success: true,
      message: '病患資料更新成功', 
      patient_id: id
    });
    
  } catch (error) {
    // 回滾交易
    if (connection) {
      try {
        await connection.rollback();
        console.log('🔙 交易已回滾');
      } catch (rollbackError) {
        console.error('回滾失敗:', rollbackError);
      }
    }
    console.error('❌ 更新病患失敗:', error);
    res.status(500).json({ 
      error: '更新失敗',
      details: error.message 
    });
  } finally {
    if (connection) {
      try {
        connection.autoCommit = true;
        await db.closeConnection(connection);
      } catch (closeError) {
        console.error('關閉連線錯誤:', closeError);
      }
    }
  }
});

// 刪除病患 - 使用 DELETE 方法
router.delete('/:id', authenticate, async (req, res) => {
  const { id } = req.params;
  
  console.log('🗑️ 刪除病患，ID:', id);

  // 檢查權限
  const allowedRoles = ['doctor', '醫師', 'DR', 'Doctor'];
  if (!allowedRoles.includes(req.user.role)) {
    return res.status(403).json({ 
      error: '權限不足，只有醫生可以刪除病患資料' 
    });
  }

  let connection;
  try {
    connection = await db.getConnection();
    connection.autoCommit = false;

    console.log('🔍 檢查病患是否存在...');
    const checkPatientSql = 'SELECT COUNT(*) as count FROM PATIENTS WHERE PATIENTID = :patient_id';
    const patientResult = await connection.execute(checkPatientSql, { patient_id: id }, { outFormat: oracledb.OUT_FORMAT_OBJECT });

    const patientCount = patientResult.rows[0].COUNT;
    console.log('   - 病患存在檢查結果:', patientCount);
    
    if (patientCount === 0) {
      return res.status(404).json({ error: '找不到要刪除的病患' });
    }

    // 步驟1: 刪除病歷相關表記錄
    console.log('🔄 刪除病歷相關記錄...');
    
    // 1.1 刪除手術記錄
    const deleteSurgerySql = 'DELETE FROM SURGERYRECORDS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :patient_id)';
    const surgeryResult = await connection.execute(deleteSurgerySql, { patient_id: id });
    console.log('✅ SURGERYRECORDS 表刪除成功，影響行數:', surgeryResult.rowsAffected);

    // 1.2 刪除住院記錄
    const deleteHospSql = 'DELETE FROM HOSPITALIZATIONRECORDS WHERE "AppointmentID" IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :patient_id)';
    const hospResult = await connection.execute(deleteHospSql, { patient_id: id });
    console.log('✅ HOSPITALIZATIONRECORDS 表刪除成功，影響行數:', hospResult.rowsAffected);

    // 1.3 刪除檢驗記錄
    const deleteTestsSql = 'DELETE FROM TESTS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :patient_id)';
    const testsResult = await connection.execute(deleteTestsSql, { patient_id: id });
    console.log('✅ TESTS 表刪除成功，影響行數:', testsResult.rowsAffected);

    // 1.4 刪除過敏記錄
    const deleteAllergySql = 'DELETE FROM ALLERGYRECORDS WHERE PATIENTID = :patient_id';
    const allergyResult = await connection.execute(deleteAllergySql, { patient_id: id });
    console.log('✅ ALLERGYRECORDS 表刪除成功，影響行數:', allergyResult.rowsAffected);

    // 步驟2: 刪除 RECORDS 表相關記錄
    console.log('🔄 刪除 RECORDS 表相關記錄...');
    const deleteRecordsSql = 'DELETE FROM RECORDS WHERE PATIENTID = :patient_id';
    const recordsResult = await connection.execute(deleteRecordsSql, { patient_id: id });
    console.log('✅ RECORDS 表刪除成功，影響行數:', recordsResult.rowsAffected);

    // 步驟3: 刪除 APPOINTMENTS 表相關記錄
    console.log('🔄 刪除 APPOINTMENTS 表相關記錄...');
    const deleteAppointmentsSql = 'DELETE FROM APPOINTMENTS WHERE PATIENTID = :patient_id';
    const appointmentsResult = await connection.execute(deleteAppointmentsSql, { patient_id: id });
    console.log('✅ APPOINTMENTS 表刪除成功，影響行數:', appointmentsResult.rowsAffected);

    // 步驟4: 最後才刪除 PATIENTS 表的主記錄
    console.log('🔄 刪除 PATIENTS 表記錄...');
    const deletePatientSql = 'DELETE FROM PATIENTS WHERE PATIENTID = :patient_id';
    const patientDeleteResult = await connection.execute(deletePatientSql, { patient_id: id });
    console.log('✅ PATIENTS 表刪除成功，影響行數:', patientDeleteResult.rowsAffected);

    await connection.commit();
    console.log('✅ 病患及其所有相關資料刪除成功');

    res.json({ 
      success: true,
      message: '病患及其所有相關資料已刪除成功',
      deleted_patient_id: id,
      deleted_records: {
        surgery: surgeryResult.rowsAffected,
        hospitalization: hospResult.rowsAffected,
        tests: testsResult.rowsAffected,
        allergy: allergyResult.rowsAffected,
        records: recordsResult.rowsAffected,
        appointments: appointmentsResult.rowsAffected
      }
    });
    
  } catch (error) {
    if (connection) {
      try {
        await connection.rollback();
        console.log('🔙 交易已回滾');
      } catch (rollbackError) {
        console.error('回滾失敗:', rollbackError);
      }
    }
    console.error('❌ 刪除病患失敗:', error);
    res.status(500).json({ 
      error: '刪除失敗',
      details: error.message 
    });
  } finally {
    if (connection) {
      try {
        connection.autoCommit = true;
        await db.closeConnection(connection);
      } catch (closeError) {
        console.error('關閉連線錯誤:', closeError);
      }
    }
  }
});

module.exports = router;

records.js

console.log('✅✅✅ 正在執行新的 records.js 檔案 ✅✅✅');

const express = require('express');
const oracledb = require('oracledb');
const router = express.Router();
const db = require('../database');
const { authenticate } = require('../middleware/auth');

// GET /api/records/:patientId - 查詢特定病患的所有病歷
router.get('/:patientId', authenticate, async (req, res) => {
    const { patientId } = req.params;
    let connection;
    try {
        connection = await db.getConnection();
        const result = {};

        console.log(`🔍 查詢病患 ${patientId} 的病歷記錄...`);

        // 平行查詢四張表，對需要關聯的表使用 JOIN
        const [surgeryRes, hospRes, allergyRes, labRes] = await Promise.all([
            // 1.手術記錄 (修正為 JOIN 查詢)
            connection.execute(
                `SELECT h.WardArea as WARDAREA, h.BedNum as BEDNUMBER, h.RoomNum as ROOMNUMBER, 
                        h.LengthOfStay as STAYDAYS, h.AdmissionDate as ADMISSIONDATE, h.ActualDischargeDate as DISCHARGEDATE,
                        h.AdmissionDiagnosis as ADMISSIONDIAGNOSIS, h.DischargeDiagnosis as DISCHARGEDIAGNOSIS
                FROM HOSPITALIZATIONRECORDS h
                JOIN APPOINTMENTS a ON h.AppointmentID = a.APPOINTMENTID
                WHERE a.PATIENTID = :id`, 
                { id: patientId },
                { outFormat: oracledb.OUT_FORMAT_OBJECT }
            ),
            // 2.住院記錄
            connection.execute(
                `SELECT h."WardArea" as WARDNAME, h."BedNum" as BEDNAME, h."RoomNum" as BEDNUMBER,
                        h."LengthOfStay" as STAYDAYS, h."AdmissionDate" as ADMISSIONDATE, 
                        h."ActualDischargeDate" as DISCHARGEDATE,
                        h."AdmissionDiagnosis" as ADMISSIONREASON, h."DischargeDiagnosis" as DISCHARGEREASON
                 FROM HOSPITALIZATIONRECORDS h
                 JOIN APPOINTMENTS a ON h."AppointmentID" = a.APPOINTMENTID
                 WHERE a.PATIENTID = :id`,
                { id: patientId },
                { outFormat: oracledb.OUT_FORMAT_OBJECT }
            ),
            // 3.過敏記錄
            connection.execute(
                `SELECT ALLERGEN, REACTIONSYMPTOMS as SYMPTOM, SEVERITY, ALLERGYNOTES as NOTES
                 FROM ALLERGYRECORDS 
                 WHERE PATIENTID = :id`, // <-- 從 ALLERYRECORDS 改為 ALLERGYRECORDS
                { id: patientId },
                { outFormat: oracledb.OUT_FORMAT_OBJECT }
            ),
            // 4.檢驗記錄 (修正為 JOIN 查詢)
            connection.execute(
                `SELECT t.TESTNAME, t.TESTDATE, t.TESTRESULT, t.TESTNOTES as SUGGESTION
                 FROM TESTS t
                 JOIN APPOINTMENTS a ON t.APPOINTMENTID = a.APPOINTMENTID
                 WHERE a.PATIENTID = :id`,
                { id: patientId },
                { outFormat: oracledb.OUT_FORMAT_OBJECT }
            )
        ]);

        // 日誌
        console.log('📊 查詢結果:');
        console.log('   - 手術記錄:', surgeryRes.rows);
        console.log('   - 住院記錄:', hospRes.rows);
        console.log('   - 過敏記錄:', allergyRes.rows);
        console.log('   - 檢驗記錄:', labRes.rows);

        // 將查詢結果整理成前端要的格式
        result.surgery = surgeryRes.rows[0] || null;
        result.hospitalization = hospRes.rows[0] || null;
        result.allergy = allergyRes.rows[0] || null;
        result.labTest = labRes.rows[0] || null;

        console.log('✅ 整理後的數據:', JSON.stringify(result, null, 2));

        // 如果所有記錄都不存在，返回 404
        if (Object.values(result).every(val => val === null)) {
            console.log('❌ 找不到該病患的任何病歷記錄');
            return res.status(404).json({ message: '找不到該病患的任何病歷記錄' });
        }
        
        res.json(result);
    } catch (err) {
        console.error('❌ 查詢病歷失敗:', err);
        res.status(500).json({ error: '伺服器內部錯誤' });
    } finally {
        if (connection) await db.closeConnection(connection);
    }
});

router.delete('/:patientId', authenticate, async (req, res) => {
    const { patientId } = req.params;
    let connection;
    try {
        connection = await db.getConnection();
        
        console.log(`🗑️ 刪除病患 ${patientId} 的所有病歷記錄 (注意: 關聯表可能刪除失敗)...`);

        await connection.execute(`DELETE FROM SURGERYRECORDS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :id)`, { id: patientId });
        await connection.execute(`DELETE FROM HOSPITALIZATIONRECORDS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :id)`, { id: patientId });
        await connection.execute(`DELETE FROM ALLERGYRECORDS WHERE PATIENTID = :id`, { id: patientId });
        await connection.execute(`DELETE FROM TESTS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :id)`, { id: patientId });
        
        await connection.commit();

        console.log('✅ 相關病歷已嘗試刪除');
        res.json({ success: true, message: '相關病歷已嘗試刪除' });
    } catch (err) {
        console.error('❌ 刪除病歷失敗:', err);
        if (connection) await connection.rollback();
        res.status(500).json({ error: '刪除病歷失敗' });
    } finally {
        if (connection) await db.closeConnection(connection);
    }
    // 步驟4: 刪除其他病歷相關表記錄
console.log('🔄 刪除其他病歷相關記錄...');

// 刪除手術記錄
const deleteSurgerySql = 'DELETE FROM SURGERYRECORDS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :patient_id)';
const surgeryResult = await connection.execute(deleteSurgerySql, { patient_id: id });
console.log('✅ SURGERYRECORDS 表刪除成功，影響行數:', surgeryResult.rowsAffected);

// 刪除住院記錄
const deleteHospSql = 'DELETE FROM HOSPITALIZATIONRECORDS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :patient_id)';
const hospResult = await connection.execute(deleteHospSql, { patient_id: id });
console.log('✅ HOSPITALIZATIONRECORDS 表刪除成功，影響行數:', hospResult.rowsAffected);

// 刪除檢驗記錄
const deleteTestsSql = 'DELETE FROM TESTS WHERE APPOINTMENTID IN (SELECT APPOINTMENTID FROM APPOINTMENTS WHERE PATIENTID = :patient_id)';
const testsResult = await connection.execute(deleteTestsSql, { patient_id: id });
console.log('✅ TESTS 表刪除成功，影響行數:', testsResult.rowsAffected);

// 刪除過敏記錄
const deleteAllergySql = 'DELETE FROM ALLERGYRECORDS WHERE PATIENTID = :patient_id';
const allergyResult = await connection.execute(deleteAllergySql, { patient_id: id });
console.log('✅ ALLERGYRECORDS 表刪除成功，影響行數:', allergyResult.rowsAffected);
});

module.exports = router;

server.js

const express = require('express');
const cors = require('cors');
const path = require('path');
const db = require('./database');
const oracledb = require('oracledb');

const app = express();
const PORT = 3001;

// 中間件
app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname, '../frontend')));

// 引入路由
const patientsRouter = require('./routes/patients');
const recordsRouter = require('./routes/records');

// 請求日誌中間件
app.use((req, res, next) => {
  console.log('📨 收到請求:', req.method, req.url);
  next();
});

// 掛載路由
app.use('/api/patients', patientsRouter);
app.use('/api/records', recordsRouter);

// 測試路由
app.get('/api/test', (req, res) => {
  console.log('✅ 測試路由被呼叫');
  res.json({ message: 'API 測試成功!', timestamp: new Date() });
});

// 主要的 Records 路由 - 修正住院記錄查詢
app.get('/api/records/:patientId', async (req, res) => {
  const { patientId } = req.params;
  console.log(`🔍 [Records路由] 查詢病患 ${patientId} 的病歷記錄...`);
  
  let connection;
  try {
    connection = await db.getConnection();
    const result = {};

    console.log('🔍 開始查詢資料庫...');

    // 平行查詢四張表
    let surgeryRes, hospRes, allergyRes, labRes;
    
    try {
      // 手術記錄
      surgeryRes = await connection.execute(
        `SELECT s.SURGERYNAME, s.SURGERYSITE as SURGERYPART, s.STAFFNOTES as NOTE, s.COMPLICATIONS 
         FROM SURGERYRECORDS s
         JOIN APPOINTMENTS a ON s.APPOINTMENTID = a.APPOINTMENTID
         WHERE a.PATIENTID = :id`, 
        { id: patientId }, 
        { outFormat: oracledb.OUT_FORMAT_OBJECT }
      );
      console.log('✅ 手術記錄查詢完成, 筆數:', surgeryRes.rows.length);
    } catch (err) {
      console.error('❌ 手術記錄查詢失敗:', err.message);
      surgeryRes = { rows: [] };
    }

    try {
       // 住院記錄(通過 APPOINTMENTS 關聯)
      hospRes = await connection.execute(
        `SELECT h."WardArea" as WARDNAME, h."BedNum" as BEDNAME, h."RoomNum" as BEDNUMBER, 
                h."LengthOfStay" as STAYDAYS, h."AdmissionDate" as ADMISSIONDATE, h."ActualDischargeDate" as DISCHARGEDATE,
                h."AdmissionDiagnosis" as ADMISSIONREASON, h."DischargeDiagnosis" as DISCHARGEREASON
         FROM HOSPITALIZATIONRECORDS h
         JOIN APPOINTMENTS a ON h."AppointmentID" = a.APPOINTMENTID 
         WHERE a.PATIENTID = :id`, // <-- 關鍵！為所有大小寫混合的欄位加上雙引號
        { id: patientId }, 
        { outFormat: oracledb.OUT_FORMAT_OBJECT }
      );
      console.log('✅ 住院記錄查詢完成, 筆數:', hospRes.rows.length);
      if (hospRes.rows.length > 0) {
        console.log('   住院記錄資料:', hospRes.rows[0]);
      }
    } catch (err) {
      console.error('❌ 住院記錄查詢失敗:', err.message);
      hospRes = { rows: [] };
    }


    try {
      // 過敏記錄
      allergyRes = await connection.execute(
        `SELECT ALLERGEN, REACTIONSYMPTOMS as SYMPTOM, SEVERITY, ALLERGYNOTES as NOTES
         FROM ALLERGYRECORDS 
         WHERE PATIENTID = :id`, 
        { id: patientId }, 
        { outFormat: oracledb.OUT_FORMAT_OBJECT }
      );
      console.log('✅ 過敏記錄查詢完成, 筆數:', allergyRes.rows.length);
    } catch (err) {
      console.error('❌ 過敏記錄查詢失敗:', err.message);
      allergyRes = { rows: [] };
    }

    try {
      // 檢驗記錄
      labRes = await connection.execute(
        `SELECT t.TESTNAME, t.TESTDATE, t.TESTRESULT, t.TESTNOTES as SUGGESTION
         FROM TESTS t
         JOIN APPOINTMENTS a ON t.APPOINTMENTID = a.APPOINTMENTID
         WHERE a.PATIENTID = :id`, 
        { id: patientId }, 
        { outFormat: oracledb.OUT_FORMAT_OBJECT }
      );
      console.log('✅ 檢驗記錄查詢完成, 筆數:', labRes.rows.length);
    } catch (err) {
      console.error('❌ 檢驗記錄查詢失敗:', err.message);
      labRes = { rows: [] };
    }

    // 將查詢結果整理成前端要的格式
    result.surgery = surgeryRes.rows[0] || null;
    result.hospitalization = hospRes.rows[0] || null;
    result.allergy = allergyRes.rows[0] || null;
    result.labTest = labRes.rows[0] || null;

    console.log('✅ 整理後的數據:', JSON.stringify(result, null, 2));
    
    console.log('✅ 成功返回病歷資料');
    res.json(result);
  } catch (err) {
    console.error('❌ 查詢病歷失敗:', err);
    res.status(500).json({ error: '伺服器內部錯誤: ' + err.message });
  } finally {
    if (connection) await db.closeConnection(connection);
  }
});

// Patients 路由
app.get('/api/patients/search', async (req, res) => {
  const { query } = req.query;
  console.log('🎯 收到搜尋請求，條件:', query);

  if (!query) {
    return res.status(400).json({ error: '請提供搜尋條件' });
  }

  let connection;
  try {
    console.log('🔗 連接資料庫...');
    connection = await db.getConnection();
    console.log('✅ 資料庫連線成功');
    
    const sql = `
      SELECT 
        p.PATIENTID as PATIENT_ID,
        p.PATIENTNAME as NAME,
        p.PATIENTGENDER as GENDER,
        TO_CHAR(p.PATIENTBIRTH, 'YYYY-MM-DD') as BIRTHDAY,
        p.BLOODTYPE as BLOOD_TYPE,
        p.PATIENTPHONE as PHONE,
        p.PATIENTADDRESS as ADDRESS,
        p.PATIENTIDENTITYNUMBER as ID_NUMBER,
        p.EMERGENCYPHONE as EMERGENCY_CONTACT,
        r.FAMILYHISTORY as FAMILY_HISTORY,
        r.HABITS as BAD_HABITS,
        r.ALLERGYHISTORY as ALLERGY
      FROM PATIENTS p
      LEFT JOIN RECORDS r ON p.PATIENTID = r.PATIENTID
      WHERE p.PATIENTNAME LIKE :query 
         OR p.PATIENTIDENTITYNUMBER LIKE :query
         OR p.PATIENTID LIKE :query
    `;
    
    const result = await connection.execute(sql, {
      query: `%${query}%`
    }, { outFormat: oracledb.OUT_FORMAT_OBJECT });
    
    console.log('✅ 查詢成功，找到', result.rows.length, '筆資料');
    
    res.json({ patients: result.rows });
    
  } catch (error) {
    console.error('❌ 搜尋病患失敗:', error);
    res.status(500).json({ error: '搜尋失敗: ' + error.message });
  } finally {
    if (connection) {
      try {
        await db.closeConnection(connection);
      } catch (closeError) {
        console.error('關閉連線錯誤:', closeError);
      }
    }
  }
});

// 提供前端頁面
app.get('/', (req, res) => {
  res.sendFile(path.join(__dirname, '../frontend/patients.html'));
});

app.get('/patients', (req, res) => {
  res.sendFile(path.join(__dirname, '../frontend/patients.html'));
});

app.get('/records', (req, res) => {
  res.sendFile(path.join(__dirname, '../frontend/records.html'));
});

app.get('/index', (req, res) => {
  res.sendFile(path.join(__dirname, '../frontend/index.html'));
});

// 404 處理
app.use((req, res, next) => {
  if (req.url.startsWith('/api/')) {
    console.log('❌ 404 - API 路徑不存在:', req.method, req.originalUrl);
    
    // 顯示可用路由
    let availableRoutes = [
      'GET /api/test',
      'GET /api/patients/search',
      'PUT /api/patients/:id',
      'DELETE /api/patients/:id',
      'GET /api/records/:patientId'
    ];
    
    return res.status(404).json({ 
      error: 'API 路徑不存在',
      method: req.method,
      path: req.originalUrl,
      available_routes: availableRoutes
    });
  }
  next();
});

// 全局錯誤處理
app.use((err, req, res, next) => {
  console.error('💥 全局錯誤:', err);
  res.status(500).json({ error: '伺服器內部錯誤' });
});

// 啟動伺服器
app.listen(PORT, () => {
  console.log('='.repeat(50));
  console.log(`🏥 醫院病患管理系統運行在 http://localhost:${PORT}`);
  console.log('📝 可用測試網址:');
  console.log(`   GET 測試: http://localhost:${PORT}/api/test`);
  console.log(`   病患搜尋測試: http://localhost:${PORT}/api/patients/search?query=張庭萱`);
  console.log(`   病歷查詢測試: http://localhost:${PORT}/api/records/12491`);
  console.log(`   前端頁面: http://localhost:${PORT}/`);
  console.log('='.repeat(50));
});

process.on('SIGINT', () => {
  console.log('\n🛑 關閉伺服器...');
  process.exit(0);
});

實際Demo影片：
結果我發現我忘記做登出的按鈕嗚嗚><所以之後有空會再加上去的 ~
整體來說還有一些需改進的地方，之後有更多時間精力會再來精進的！
https://drive.google.com/file/d/1FX7BbOymmbNnKszXO1Z_gO17Np1iBnaS/view?usp=sharing

今天的分享就到這邊，明天會進行對整個30天鐵人賽自我挑戰的總結心得！